[업데이트]Amazon Redshift에서 QUALIFY를 지원하게 되었습니다.

안녕하세요 DA사업본부 송영진입니다. 오늘은 Amazon Redshift에서 새롭게 업데이트되어 사용할 수 있게 된 QUALIFY에 대해 알아보도록 하겠습니다. QUALIFY란 무엇일까요?

QUALIFY 절은 사용자가 지정한 검색 조건에 따라 이전에 계산된 윈도우 함수의 결과를 필터링합니다. 이 절을 사용하면 하위 쿼리를 사용하지 않고 윈도우 함수의 결과에 필터링 조건을 적용할 수 있습니다.

이는 조건을 적용하여 WHERE 절의 행을 추가로 필터링하는 HAVING 절과 유사합니다. QUALIFY와 HAVING의 차이점은 QUALIFY 절의 필터링된 결과가 데이터에 대해 윈도우 함수를 실행한 결과를 기반으로 할 수 있다는 것입니다. 한 쿼리에서 QUALIFY 절과 HAVING 절을 모두 사용할 수 있습니다.

HAVING 절과 유사하지만 HAVING 절은 집계함수인 SUM이나 COUNT와 같은 집계함수나, GROUP BY 절의 결과를 사용하는 반면, QUALIFY는 WINDOW 함수의 결과를 필터링한다는 점이 주된 차이점이 되겠습니다. 결국 이게 무슨 뜻이냐하면 기존에 서브쿼리를 사용하여 길게 작성해야했던 SQL 문을 더 짧게 작성해서 가독성에 도움이 된다는 이야기죠!

그럼 이제 직접 확인해보면서 알아보겠습니다.

사용방법

QUALIFY condition

위와 같이 QUALIFY 조건의 형태로 사용하게 됩니다. 이것만으로는 전혀 모르겠으니 공식 문서에 있는 예제를 사용하여 좀 더 이해를 해보겠습니다.

공식 문서에서 예제로 사용하는 테이블을 생성하여, 다음과 같은 데이터를 사용하게 됩니다.

create table store_sales (ss_sold_date date, ss_sold_time time, 
               ss_item text, ss_sales_price float);
insert into store_sales values ('2022-01-01', '09:00:00', 'Product 1', 100.0),
                               ('2022-01-01', '11:00:00', 'Product 2', 500.0),
                               ('2022-01-01', '15:00:00', 'Product 3', 20.0),
                               ('2022-01-01', '17:00:00', 'Product 4', 1000.0),
                               ('2022-01-01', '18:00:00', 'Product 5', 30.0),
                               ('2022-01-02', '10:00:00', 'Product 6', 5000.0),
                               ('2022-01-02', '16:00:00', 'Product 7', 5.0);

ss_sold_date	ss_sold_time	ss_item	ss_sales_price
2022-01-01	9:00:00	Product 1	100
2022-01-01	11:00:00	Product 2	500
2022-01-01	15:00:00	Product 3	20
2022-01-01	17:00:00	Product 4	1000
2022-01-01	18:00:00	Product 5	30
2022-01-02	10:00:00	Product 6	5000
2022-01-02	16:00:00	Product 7	5

다음과 같은 조건으로 SQL을 작성하여 결과를 확인해보겠습니다.

• 12시 이후에 판매된 상품
• 가격순으로 2번째 까지 비싼 상품

이러한 결과값을 도출하게 됩니다.

ss_sold_date	ss_sold_time	ss_item	ss_sales_price
2022-01-01	17:00:00	Product 4	1000
2022-01-01	18:00:00	Product 5	30
2022-01-02	16:00:00	Product 7	5

QUALIFY를 사용하지 않았을 때의 SQL

기존의 방법으로는 WHERE의 조건에 WINDOW 함수를 사용할 수 없기 때문에 FROM 내부에서 서브쿼리를 만들어서 사용했는데요, 이거 처음보면 알아보기가 어렵습니다...

SELECT
    ss_sold_date,
    ss_sold_time,
    ss_item,
    ss_sales_price
FROM (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY ss_sold_date ORDER BY ss_sales_price DESC) AS row_num
  FROM
    store_sales 
  WHERE
    ss_sold_time > TIME '12:00:00'
) subquery
WHERE
  subquery.row_num <= 2;

다른 방법으로는 WITH 구문을 사용하여 임시 테이블을 만드는 방법도 있죠. 저는 위의 방법보다 이 쪽을 선호합니다.

WITH tmp_result AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY ss_sold_date ORDER BY ss_sales_price DESC) AS row_num
  FROM
    store_sales 
  WHERE
    ss_sold_time > TIME '12:00:00'
)
SELECT
  ss_sold_date,
  ss_sold_time,
  ss_item,
  ss_sales_price
FROM
  tmp_result
WHERE
  row_num <= 2;

위에서 따라가면 되지만 테이블이 2개나 나오기 때문에 한눈에 바로 알아보기는 쉽지 않습니다.

QUALIFY를 사용한 SQL

자 그러면 QUALIFY를 사용하면 간략하게 만들 수 있다고 했으니 어떻게 SQL이 바뀌는지 알아봐야겠죠? 다음과 같습니다.

SELECT *
FROM store_sales 
WHERE ss_sold_time > TIME '12:00:00'
QUALIFY row_number()
OVER (PARTITION BY ss_sold_date ORDER BY ss_sales_price DESC) <= 2;

굉장히 심플해졌죠? WHERE의 조건에서는 똑같이 12시 이후로 지정해놓고, WINDOW 함수로 ss_sold_date인 날짜마다 가격을 DESC 순으로 지정한 row_number()를 두 번째 까지 가져와서 SELECT 한다가 되겠습니다.

참고

위의 SQL은 공식 도큐먼트와 같은 사업본부의 新納님의 블로그를 참고했습니다.

마지막으로

새롭게 업데이트된 QUALIFY에 대해서 알아보았습니다. 저는 주로 WINDOW 함수로 유저의 최신 레코드를 가져오기 위해서 row_number()를 사용하곤 하는데요, 이 때마다 SQL이 복잡해져서 나중에 다시 읽을 때 어디부터 읽어야하더라... 하고 한참 찾곤 합니다.

이번에 사용가능하게된 QUALIFY를 이용해서 작성하면 어디부터 봐야할지 찾아보는 시간은 조금 줄긴 할 것 같네요. WINDOW 함수 자체가 한눈에 이해하긴 어렵기 때문에 코드를 해석하는 시간은 여전히 걸리겠지만 긴 SQL 문을 보고 한숨쉬는건 적어질 것 같습니다!

여러분들도 코드를 줄이기 위해서 QUALIFY를 사용해보시는건 어떨까요? 조금이라도 도움이 되셨길 바랍니다. 감사합니다!